import pymysql
import re
import os

# 数据库配置信息
DB_CONFIG = {
    'host': '172.19.251.204',  # WSL 2 的 IP 地址
    'user': 'meiduo_user',
    'password': '123456',      # meiduo_user 的密码
    'port': 3306,
    'database': 'meiduo_mall'  # 指定要使用的数据库
}

def execute_sql_file(cursor, file_path):
    with open(file_path, 'r', encoding='utf8') as f:
        sql_content = f.read()

    # 分割 SQL 文件为单独的命令
    commands = [cmd.strip() for cmd in re.split(r';\s*', sql_content) if cmd.strip()]

    for command in commands:
        try:
            # 直接执行 SQL 命令
            cursor.execute(command)
        except pymysql.Error as e:
            print(f"执行 SQL 命令 {command[:50]}... 时出错: {e}")

try:
    # 连接数据库
    connection = pymysql.connect(**DB_CONFIG)
    with connection.cursor() as cursor:
        # 构建 SQL 文件的绝对路径，使用原始字符串避免转义序列问题
        sql_file_path = '/mnt/d/django_proj/django_prac/python_for_config/goods_data.sql'
        
        # 检查文件是否存在
        if not os.path.exists(sql_file_path):
            raise FileNotFoundError(f"SQL file {sql_file_path} does not exist.")
        
        # 执行 SQL 文件中的命令
        execute_sql_file(cursor, sql_file_path)
    
    # 提交事务
    connection.commit()
    print("SQL 语句执行成功")
except pymysql.Error as e:
    print(f"执行 SQL 语句时出错: {e}")
    if 'connection' in locals() and connection:
        connection.rollback()
finally:
    if 'connection' in locals() and connection:
        connection.close()